package com.example.hello.mapper;

import com.example.hello.pojo.*;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;

/**
 * MyBatis 注解方式操作用户表
 */
@Mapper
public interface UserMapper {

    /**
     * 根据手机号查找用户（手机号唯一）
     */
    @Select("SELECT * FROM user WHERE phone = #{phone} LIMIT 1")
    User findByPhone(@Param("phone") String phone);

    /**
     * 插入用户记录
     */
    @Insert("INSERT INTO user (phone, password, status, role, created_at, updated_at, last_login_at, nickname, avatar, gender, birthday, locale, last_login_ip) " +
            "VALUES (#{phone}, #{password}, #{status}, #{role}, #{createdAt}, #{updatedAt}, #{lastLoginAt}, #{nickname}, #{avatar}, #{gender}, #{birthday}, #{locale}, #{lastLoginIp})")
    int insertUser(User user);

    /**
     * 更新用户最后登录时间
     */
    @Update("UPDATE user SET last_login_at = #{lastLoginAt} WHERE id = #{id}")
    int updateLastLoginTime(@Param("id") Long id, @Param("lastLoginAt") java.util.Date lastLoginAt);

    /**
     * 根据手机号更新用户密码，并更新时间
     */
    @Update("UPDATE user SET password = #{password}, updated_at = NOW() WHERE phone = #{phone}")
    int updatePasswordByPhone(@Param("phone") String phone, @Param("password") String password);


    /**
     * 修改用户信息
     * @param loginInfo
     */
    @Update("update user set nickname = #{nickname}, status = #{status}, avatar = #{avatar}, gender = #{gender}, locale = #{locale},updated_at = now() where id = #{id}")
    void uodateById(LoginInfo loginInfo);

    /**
     * 查询所有用户的图书借阅情况
     * @return
     */
    @Select("select u.id,u.nickname,u.phone,u.avatar,u.status,count(*) borrowBookNum " +
            "from user_book ub join user u on ub.user_id = u.id " +
            "where ub.return_date is null and " +
            "(u.nickname like concat('%', #{requestParam}, '%') " +
            "or u.phone like concat('%', #{requestParam}, '%'))" +
            " group by u.id order by count(*) desc")
    List<UserBorrowBookInfo> findAllUserBorrowBookInfo(PageQueryParam pageQueryParam);

    @Update("update user set status = #{status} where id = #{id}")
    void updateUserStatus(Integer id, Integer status);

    /**
     * 根据主键 ID 查询用户
     */
    @Select("SELECT " +
            "id, phone, status, password, role, " +
            "created_at AS createdAt, updated_at AS updatedAt, last_login_at AS lastLoginAt, " +
            "nickname, avatar, gender, birthday, locale, last_login_ip AS lastLoginIp, " +
            "account_money AS accountMoney, overdue_pay AS overduePay, credit ,certificate_id " +
            "FROM `user` WHERE id = #{id}")
    User getById(Long id);

    /** 更新用户信誉，并保持 0~100 */
    @Update("UPDATE `user` " +
            "SET credit = LEAST(100, GREATEST(0, credit + #{delta})) " +
            "WHERE id = #{userId}")
    void updateCredit(@Param("userId") Long userId, @Param("delta") int delta);

    /** 获取所有用户 ID，用于每周恢复 */
    @Select("SELECT id FROM `user`")
    List<Long> listAllUserIds();

    /** 查询某用户当前信誉 */
    @Select("SELECT credit FROM `user` WHERE id = #{userId}")
    Integer getCredit(@Param("userId") Long userId);





    @Select("SELECT account_money FROM `user` WHERE id = #{userId}")
    BigDecimal getAccountMoney(@Param("userId") Long userId);

    @Select("SELECT overdue_pay FROM `user` WHERE id = #{userId}")
    BigDecimal getOverduePay(@Param("userId") Long userId);

    @Update("UPDATE `user` SET account_money = #{newBalance} WHERE id = #{userId}")
    void updateAccountMoney(@Param("userId") Long userId,
                            @Param("newBalance") BigDecimal newBalance);

    @Update("UPDATE `user` " +
            "SET overdue_pay = overdue_pay + #{delta} " +
            "WHERE id = #{userId}")
    void increaseOverduePay(@Param("userId") Long userId,
                            @Param("delta") BigDecimal delta);

    @Update("UPDATE `user` " +
            "SET account_money = account_money - #{amt}, " +
            "    overdue_pay = overdue_pay - #{amt} " +
            "WHERE id = #{userId}")
    void payOverdue(@Param("userId") Long userId,
                    @Param("amt") BigDecimal amt);

    /** 每天 0 点跑：统计所有逾期未还书籍数 */
    @Select("SELECT ub.user_id AS userId, COUNT(*) AS count " +
            "FROM user_book ub " +
            "WHERE ub.return_date IS NULL " +
            "  AND DATE_ADD(ub.borrow_date, INTERVAL ub.max_borrow_day DAY) < CURRENT_DATE() " +
            "GROUP BY ub.user_id")
    List<OverdueCount> listOverdueCounts();
}
